package instrument;

import java.sql.*;

public class DBUtil {
    private static final String URL = "jdbc:sqlite:cinema.db";

    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL);
    }

    public static void initDB() {
        String[] sqls = new String[]{
                "CREATE TABLE IF NOT EXISTS users(" +
                        "id INTEGER PRIMARY KEY," +
                        "username TEXT UNIQUE NOT NULL," +
                        "password TEXT NOT NULL," +
                        "user_type TEXT NOT NULL," +
                        "telephone TEXT UNIQUE NOT NULL," +
                        "register_time TEXT NOT NULL," +
                        "need_change_password BOOLEAN NOT NULL);",
                "CREATE TABLE IF NOT EXISTS movies(" +
                        "name TEXT PRIMARY KEY," +
                        "director TEXT," +
                        "actors TEXT," +
                        "intro TEXT," +
                        "duration INTEGER," +
                        "score REAL," +
                        "is_showing BOOLEAN);",
                "CREATE TABLE IF NOT EXISTS shows(" +
                        "id INTEGER PRIMARY KEY," +
                        "movie_name TEXT," +
                        "hall TEXT," +
                        "show_time TEXT," +
                        "price REAL," +
                        "row_count INTEGER," +
                        "col_count INTEGER," +
                        "seats TEXT," +
                        "FOREIGN KEY(movie_name) REFERENCES movies(name));",
                "CREATE TABLE IF NOT EXISTS sale_records(" +
                        "ticket_id TEXT PRIMARY KEY," +
                        "show_id INTEGER," +
                        "seat_row INTEGER," +
                        "seat_col INTEGER," +
                        "price REAL," +
                        "sale_time TEXT," +
                        "is_refunded BOOLEAN," +
                        "refund_time TEXT," +
                        "refund_amount REAL," +
                        "FOREIGN KEY(show_id) REFERENCES shows(id));"
        };
        try (Connection c = getConn(); Statement s = c.createStatement()) {
            for (String sql : sqls) s.addBatch(sql);
            s.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}